/** 
*
* @author  jiwf 
* @date 创建时间：2016年10月17日 下午9:24:23 
* @version 1.0 
* @since  
*  
*/
package com.xbstar.web;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;


import org.apache.tomcat.jdbc.pool.ConnectionPool;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.xbstar.bean.AffixInfo;
import com.xbstar.bean.DestinationInfor;
import com.xbstar.bean.RouteInfo;
import com.xbstar.dao.AffixInfoRepository;
import com.xbstar.dao.DestinationRepository;
import com.xbstar.dao.RouteInfoRepository;
import com.xbstar.dao.TripRouteInfoRepository;
import com.xbstar.tools.BeanUtils;
import com.xbstar.tools.Timer;

@RestController
@CrossOrigin
@RequestMapping("/route")
public class RouteController {
	@Autowired
	private RouteInfoRepository routeRe;
	@Autowired
	private TripRouteInfoRepository tripRe;
	@Autowired
	DestinationRepository destRe;
	@Autowired
    DataSource dataSource;
	@Autowired 
	AffixInfoRepository affixRe;
	@RequestMapping("/add")
	public String addRoute(HttpServletRequest req){
		RouteInfo routeinfo=new RouteInfo();
		JSONObject json=new JSONObject();
//		routeinfo.setName(req.getParameter("name"));
//		routeinfo.setDescription(req.getParameter("description"));
//		routeinfo.setTripBelongTo(req.getParameter("tripBelongTo"));
//		routeinfo.setTripRouteInfo(req.getParameter("tripRouteInfo"));
//		routeinfo.setDestinationId(Long.parseLong(req.getParameter("destinationId")));
//		routeinfo.setType(req.getParameter("type"));
//		routeinfo.setLeaderName(req.getParameter("leaderName"));
//		routeinfo.setLeaderName(req.getParameter("leaderName"));
		try {
			routeinfo=BeanUtils.copyRequestToObj(routeinfo, req);
			routeRe.save(routeinfo);
			json.put(UserPackage.STATUS, UserPackage.SUCCESS);
		} catch (Exception e) {
			e.printStackTrace();
			json.put(UserPackage.STATUS, UserPackage.SERVER_ERROR);
		}
		return json.toString();
	}
	@RequestMapping("/del")
	public String delRoute(@RequestParam("id")Long id){
		JSONObject json=new JSONObject();
		try {
			routeRe.delete(id);
			json.put(UserPackage.STATUS, UserPackage.SUCCESS);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			json.put(UserPackage.STATUS, UserPackage.REDO);
			e.printStackTrace();
		}
		return json.toString();
	}
	@RequestMapping("/edit")
	public String editRoute(@RequestParam("id")Long id,HttpServletRequest req){
		JSONObject json=new JSONObject();
		try {
			RouteInfo routeinfo=routeRe.findOne(id);
			routeinfo=BeanUtils.copyRequestToObj(routeinfo, req);
			routeinfo.setUpdatedTime(Timer.getCurrentString());
			routeRe.save(routeinfo);
			json.put(UserPackage.STATUS, UserPackage.SUCCESS);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			json.put(UserPackage.STATUS, UserPackage.REDO);
			e.printStackTrace();
		}
		return json.toString();
	}
	@RequestMapping("/getall")
	public String getAllRoute(){
		JSONObject json=new JSONObject();
		try {
			  Connection conn  = dataSource.getConnection();
			  String sqlString="SELECT tripRouteId,affixBelongTo,tripBelongTo,affixInfoId,url,description FROM triprouteaffixview where tripBelongTo=?";
		    List<RouteInfo>result=(List<RouteInfo>) routeRe.findAll();
		    JSONArray reuslt=new JSONArray();
		    result.stream().forEach(route->{
		    	String tripBelongTo=route.getTripBelongTo();
		    	//JSONObject tjson=route.getJSON();
		    	JSONObject tjson=BeanUtils.getAJsonFromObj(route, null);
		    	List<AffixInfo>affixArr=affixRe.findALLByBelongTo(route.getAffixBelongTo());
		    	if(affixArr.size()>0){
		    		tjson.put("imgSrc", affixArr.get(0).getUrl());
		    	}
		    	JSONArray array=new JSONArray();
		    	try {
					PreparedStatement ps = conn.prepareStatement(sqlString);
					ps.setString(1, tripBelongTo);
					ResultSet rs = ps.executeQuery();
					  while (rs.next()) {
						  JSONObject obj=new JSONObject();
						  obj.put("tripRouteId", rs.getString("tripRouteId"));
						  obj.put("affixBelongTo", rs.getString("affixBelongTo"));
						  obj.put("tripBelongTo", rs.getString("tripBelongTo"));
						  obj.put("affixInfoId", rs.getString("affixInfoId"));
						  String urls=rs.getString("url")==null?"11":rs.getString("url");
						  obj.put("imgSrc", urls);
						  obj.put("description", rs.getString("description"));
						  array.put(obj);
		                }
					  rs.close();
		              ps.close();
		              
				} catch (Exception e) {
					e.printStackTrace();
				}
		    	tjson.put("triparray", array);
		    	reuslt.put(tjson);
		    });
		    conn.close();
		    json.put(UserPackage.STATUS, UserPackage.SUCCESS);
		    json.put(UserPackage.DATA,reuslt);
		} catch (Exception e) {
			json.put(UserPackage.STATUS, UserPackage.SERVER_ERROR);
			e.printStackTrace();
		}
		return json.toString();
	}
	@RequestMapping("/mainroute")
	public String getMainRoutes(){
		JSONObject json=new JSONObject();
		JSONArray array=new JSONArray();
		List<RouteInfo>routeArrr= (List<RouteInfo>) routeRe.findAll();
		if(routeArrr.size()<1){
			json.put(UserPackage.STATUS, UserPackage.EMPTY);
		}else{
			routeArrr.stream().forEach(route->{
				JSONObject tjson=BeanUtils.getAJsonFromObj(route, new String[]{"id","name"});
				array.put(tjson);
			});
			 json.put(UserPackage.STATUS, "200");
			 json.put(UserPackage.DATA, array);
		}
		return json.toString();
	}
	@RequestMapping("/getby/cityid")
	public String getByCityId(@RequestParam("destId")Long destId){
		JSONObject json=new JSONObject();
		JSONArray wayArr=new JSONArray();
		List<RouteInfo>routeArr=routeRe.findByDestinationIdOrderByUpdatedTime(destId);
		routeArr.stream().forEach(route->{
			JSONObject tjson=BeanUtils.getAJsonFromObj(route, new String[]{"id","name","leaderName","cost","description"});
			 String orderStr="select COUNT(*) from user_order WHERE route_info_id=?";
			 try {
				 Connection conn=dataSource.getConnection();
				 PreparedStatement ps = conn.prepareStatement(orderStr);
				 ps.setLong(1, route.getId());
				 ResultSet rs =ps.executeQuery();
				 while (rs.next()) {
					 tjson.put("orderNum",  rs.getString(1)==null?"0":rs.getString(1));
	                }
				  rs.close();
	              ps.close();
	              conn.close();
	              List<AffixInfo>affixarr=affixRe.findALLByBelongTo(route.getAffixBelongTo());
	              if (affixarr.size()>0) {
					tjson.put("url", affixarr.get(0).getUrl()==null?"11":affixarr.get(0).getUrl());
				  }else{
					  tjson.put("url", "11");
				  }
	              wayArr.put(tjson);
			} catch (Exception e) {
				// TODO: handle exception
			}
		});
		json.put("status", "200");
		json.put(UserPackage.DATA, wayArr);
		return json.toString();
	}
	@RequestMapping("/getdetail")
	public String getDeatailById(@RequestParam("id")Long id){
		JSONObject json=new JSONObject();
		RouteInfo route=routeRe.findOne(id);
		DestinationInfor dest=destRe.findOne(route.getDestinationId());
		JSONObject tjson=BeanUtils.getAJsonFromObj(route, null);
		tjson.put("cityName", dest.getCityName());
		tjson.put("country", dest.getCountry());
		tjson.put("province", dest.getProvince());
		List<AffixInfo>affixArr=affixRe.findALLByBelongTo(route.getAffixBelongTo());
    	if(affixArr.size()>0){
    		tjson.put("url", affixArr.get(0).getUrl()==null?"11":affixArr.get(0).getUrl());
    	}else{
    		tjson.put("url", "11");
    	}
    	JSONArray array=new JSONArray();
    	try {
    		Connection conn=dataSource.getConnection();
    		 String sqlString="SELECT tripRouteId,affixBelongTo,tripBelongTo,affixInfoId,url,description FROM triprouteaffixview where tripBelongTo=?";
			PreparedStatement ps = conn.prepareStatement(sqlString);
			ps.setString(1, route.getTripBelongTo());
			ResultSet rs = ps.executeQuery();
			  while (rs.next()) {
				  JSONObject obj=new JSONObject();
				  obj.put("tripRouteId", rs.getString("tripRouteId"));
				  obj.put("affixBelongTo", rs.getString("affixBelongTo"));
				  obj.put("tripBelongTo", rs.getString("tripBelongTo"));
				  obj.put("affixInfoId", rs.getString("affixInfoId"));
				  String urls=rs.getString("url")==null?"11":rs.getString("url");
				  obj.put("imgSrc", urls);
				  obj.put("description", rs.getString("description"));
				  array.put(obj);
                }
			  rs.close();
              ps.close();
              conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
    	JSONArray commentArr=new JSONArray();
    	try {
    		Connection conn=dataSource.getConnection();
    		 String sqlString="SELECT userId,nickName,headImgUrl,commentLevel,content,submitTime from userroutecommentview WHERE routeId=? and isOk=1";
			PreparedStatement ps = conn.prepareStatement(sqlString);
			ps.setLong(1, route.getId());
			ResultSet rs = ps.executeQuery();
			  while (rs.next()) {
				  JSONObject obj=new JSONObject();
				  obj.put("userId", rs.getString("userId"));
				  obj.put("nickName", rs.getString("nickName"));
				  obj.put("content", rs.getString("content"));
				  obj.put("commentLevel", Integer.parseInt(rs.getString("commentLevel")));
				  String urls=rs.getString("headImgUrl")==null?"11":rs.getString("headImgUrl");
				  obj.put("headImgUrl", urls);
				  obj.put("submitTime", rs.getString("submitTime"));
				  commentArr.put(obj);
                }
			  rs.close();
              ps.close();
              conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
    	tjson.put("triparray", array);
    	tjson.put("commentArr", commentArr);
    	tjson.put("commentNum", commentArr.length());
    	json.put("status", "200");
    	json.put("data", tjson);
		return json.toString();
	}
}
